﻿--获得本单位离职人员数据
CREATE PROCEDURE [dbo].[proc_Employee_Dimission_GetList_G]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@username nvarchar(50),
	@ename nvarchar(50),
	@apply nvarchar(4),
	@audit nvarchar(4),
	@cid varchar(400)
)
as

declare @sql nvarchar (4000)
DECLARE @te NVARCHAR(2000)
SET @te=''
IF(@cid<>'0')
SET @te=@te+'e.companyid in ('+@cid+')'
ELSE
SET @te=@te+'e.id<>0 and e.companyid is not null'

IF(@apply='0')
SET @te=@te+' and ee.proposer is null '
IF(@apply='1')
SET @te=@te+' and ee.proposer is not null '

if(@ename<>'%')	
SET @te=@te+' and e.ename like ''%'+@ename+'%'' '

if(@audit<>'%')
set @te=@te+' and (case when ee.audit=1 then 1	
  when ee.audit=0 and ee.auditperson is null then 0	 when ee.audit=0 
  and ee.auditperson is not null then 2 end) = '+@audit+' '


BEGIN				

	set @sql='
	
	

;WITH list AS (select ROW_NUMBER() OVER (order by ee.audit,EE.LizhiDate Desc ) AS Row,
	 EE.Id, (select ENumber from Employee E where EE.EId=E.Id) as ENumber, 
  (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber, EE.EId, 
  (select EName from Employee E where EE.EId=E.Id) as EName, 
  (select Sex from Employee E where EE.EId=E.Id) as Sex, 
  (select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
   (select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName, 
   (select Birthday from Employee E where EE.EId=E.Id) as Birthday, 
   (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education, 
   (select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName, 
  
   EE.Audit, EE. AuditPerson, EE.AuditTime, EE.Proposer,
    ee.LizhiDate 
    from employee_Dimission EE inner join Employee E on EE.EId=E.Id where '+@te+' 	
     
)

select *,(SELECT COUNT(0) FROM list ) AS coun from list  
where row between '+Convert(varchar,@startIndex)+' and '+convert(varchar,@endIndex)+'



	
	
	
	
	
	'
	
	
	
	--set @sql =
	--	+ ' declare @indextable table(id int identity(1,1),nid int)'
	--	+ ' insert into @indextable(nid)'
	--	+ ' select EE.id from employee_Dimission EE inner join Employee E on EE.EId=E.Id'
	--    + ' where '
	--	+  @te
	--	+ '	and e.ename like ''%'+@ename+'%'''		
	--	+ ' and (case when ee.audit=1 then 1'
	--	+ '	 when ee.audit=0 and ee.auditperson is null then 0'
	--	+ '	 when ee.audit=0 and ee.auditperson is not null then 2 end) like ''%'+@audit+'%'''
	--	+ ' order by ee.audit,EE.LizhiDate Desc'
	--	+ ' select' 
	--	+ ' EE.Id,'
	--	+ ' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
	--	+ ' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
	--	+ ' EE.EId,'
	--	+ ' (select EName from Employee E where EE.EId=E.Id) as EName,'
	--	+ ' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
	--	+ ' (select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,'
	--	+ ' (select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,'
	--	+ ' (select Birthday from Employee E where EE.EId=E.Id) as Birthday,'
	--	+ ' (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,'
	--	+ ' (select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,'
	--	+ ' (SELECT COUNT(0) FROM @indextable ) AS coun,'
	--	+ ' EE.Audit,'
	--	+ ' EE. AuditPerson,'
	--	+ ' EE.AuditTime,'
	--	+ ' EE.Proposer,'
	--	+ ' ee.LizhiDate'

	--	+ ' from employee_Dimission EE'
	--	+ '	inner join @indextable t on '
	--	+ ' EE.id=t.nid	 '
	--	+ ' where t.id between '+ @startIndex +' and '+ @endIndex 
	--	+ ' order by t.id'	
		
		
		
		
		
		
		
		
	END

EXEC(@sql)
print(@sql)


RETURN
